CREATE SCHEMA [ucg];
GO

CREATE TABLE [ucg].[Game]
( 
    GameID bigint identity(1,1) NOT NULL,
	Name nvarchar(128) NOT NULL,
	CardSchema xml NOT NULL
	CONSTRAINT pkGame PRIMARY KEY (GameID)
);
GO

CREATE TABLE [ucg].[Block]
(
	BlockID bigint identity(1,1) NOT NULL,
	GameID bigint NOT NULL,
	Name nvarchar(128) NOT NULL
	CONSTRAINT pkBlock PRIMARY KEY (BlockID)
	CONSTRAINT fkBlockGame FOREIGN KEY (GameID) REFERENCES [ucg].[Game](GameID)
);
GO

CREATE TABLE [ucg].[Set]
(
	SetID bigint identity(1,1) NOT NULL,
	GameID bigint NOT NULL,
	BlockID bigint NULL,
	Name nvarchar(128) NOT NULL,
	ReleaseDate datetime NULL,
	IsComplete bit NOT NULL
	CONSTRAINT pkSet PRIMARY KEY (SetID)
	CONSTRAINT fkSetGame FOREIGN KEY (GameID) REFERENCES [ucg].[Game](GameID),
	CONSTRAINT fkSetBlock FOREIGN KEY (BlockID) REFERENCES [ucg].[Block](BlockID)
);
GO

CREATE TABLE [ucg].[Card]
(
	CardID bigint identity (1,1) NOT NULL,
	Data xml NOT NULL
	CONSTRAINT pkCard PRIMARY KEY (CardID)
);
GO

CREATE TABLE [ucg].[CardSet]
(
	CardID bigint NOT NULL,
	SetID bigint NOT NULL,
	[Image] varchar(255) NULL,
	[BackImage] varchar(255) NULL,
	FlavorText nvarchar(4000) NULL,
	Artist nvarchar(128)
	CONSTRAINT pkCardSET PRIMARY KEY (CardID, SetID)
	CONSTRAINT fkCardSetCard FOREIGN KEY (CardID) REFERENCES [ucg].[Card](CardID),
	CONSTRAINT fkCardSetSet FOREIGN KEY (SetID) REFERENCES [ucg].[Set](SetID)
);
GO

